/*
 *接口编写处...
*如果接口需要做Action的权限验证，请在Action上使用属性
*如: [ApiActionPermission("big_data_sales_order",Enums.ActionPermissionOptions.Search)]
 */
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.AspNetCore.Http;
using VOL.Entity.DomainModels;
using VOL.JIRUI.IServices;
using SapNwRfc;
using VOL.Core.Configuration;
using System.Globalization;
using System.Linq;
using VOL.Core.DBManager;
using Castle.Core.Internal;
using VOL.Core.Filters;
using Microsoft.AspNetCore.Authorization;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Logical;
using VOL.Core.Extensions;
using StackExchange.Redis;
using OfficeOpenXml.Style;

namespace VOL.JIRUI.Controllers
{
    public partial class big_data_sales_orderController
    {
        private readonly Ibig_data_sales_orderService _service;//访问业务代码
        private readonly IHttpContextAccessor _httpContextAccessor;

        [ActivatorUtilitiesConstructor]
        public big_data_sales_orderController(
            Ibig_data_sales_orderService service,
            IHttpContextAccessor httpContextAccessor
        )
        : base(service)
        {
            _service = service;
            _httpContextAccessor = httpContextAccessor;
        }
        /// <summary>
        /// 从SAP系统里获取销售订单 （交货单） 
        /// 数据源SAP事务码 ZRFI9006
        /// </summary>
        /// <param name="startDate">开始日期</param>
        /// <param name="enddate">结束日期</param>
        /// <returns></returns> 20200101
        /// [ApiTask]
        [HttpGet,HttpPost, Route("getSapOrderInfo"), AllowAnonymous]
        ///[ApiActionPermission()]
        public dynamic getSapOrderInfo()
        {
            // 从参数表中获取对应的参数值
            big_data_execute_parameter big_data_execute_parameter = DBServerProvider.DbContext.Set<big_data_execute_parameter>().Where(x => x.type == "getSapOrderInfo").FirstOrDefault();
            string startDate = big_data_execute_parameter.parameter;
            string enddate = big_data_execute_parameter.parameter1;
            string newDate = DateTime.Now.ToString("yyyyMMdd", CultureInfo.InvariantCulture);
            if (big_data_execute_parameter.manual_setting == 0 && !startDate.Equal(newDate))
            {
                startDate = newDate;
                enddate = newDate;
            }
            string connectionString = AppSetting.GetSection("SAP_ECC_CONN")["connstr"];
            using var connection = new SapConnection(connectionString);
            connection.Connect();
            using var someFunction = connection.CreateFunction("Z_GET_SO");//接口名
            List<SomeFunctionitem> list = new List<SomeFunctionitem>();
            var result = someFunction.Invoke<SomeFunctionResult>(new SomeFunctionParameters
            {
                START_DATE = startDate,
                END_DATE = enddate,
            });
            for (int ii = 0; ii < result.ZRETURN_SO.Length; ii++)
            {
                list.Add(result.ZRETURN_SO[ii]);
            }
            for (int i = 0; i < list.Count; i++)
            {
                string dms_order_no = list[i].BSTKD;//DMS销售订单号
                List<big_data_delivery_note> sales_order_list = DBServerProvider.DbContext.Set<big_data_delivery_note>().Where(w => w.sap_sales_voucher == list[i].VBELN_B).ToList();
                if (sales_order_list != null && sales_order_list.Count > 0)//有值：更新
                {
                    big_data_delivery_note delivery = sales_order_list[0];
                    delivery.sap_order_no = list[i].VBELN_S;//SAP销售订单号
                    delivery.sap_sales_voucher = list[i].VBELN_B; //SAP销售开票凭证
                    delivery.sap_sales_delivery_order_no = list[i].VBELN_J;//SAP销售交货单号
                    delivery.sap_dealer_customer_no = list[i].KUNNR;//SAP经销商客户的编号
                    delivery.dealer_code = list[i].AG_KUNNR;//运达方经销商ERP号
                    delivery.dealer_name = list[i].AG_NAME;//运达方经销商名称
                    delivery.sold_dealer_code = list[i].WE_KUNNR;//售达方经销商ERP号
                    delivery.sold_dealer_name = list[i].WE_NAME;//售达方经销商名称
                    delivery.vin = list[i].SERNR;//SAP序列号（vin）
                    if (!list[i].LTRMI.IsNullOrEmpty() && !list[i].LTRMI.Equals("00000000"))
                    {
                        delivery.in_stock_date = DateTime.ParseExact(list[i].LTRMI, "yyyyMMdd", CultureInfo.CurrentCulture);//入库时间
                    }  
                    delivery.dms_order_no = list[i].BSTKD;//DMS销售订单号
                    delivery.MATNR = list[i].MATNR;//物料编码
                    delivery.Modifier = "SAPUPDATE";
                    delivery.ModifyDate = DateTime.Now;
                    delivery.AUFNR = list[i].AUFNR;
                    if (!list[i].BUDAT.IsNullOrEmpty() && !list[i].BUDAT.Equals("00000000"))
                    {
                        delivery.BUDAT = DateTime.ParseExact(list[i].BUDAT, "yyyyMMdd", CultureInfo.CurrentCulture);//过账日期
                    }
                   /* if (!list[i].LFIMG.IsNullOrEmpty() && !list[i].LFIMG.Equals("00000000"))
                    {
                        delivery.LFIMG = Convert.ToInt32(Convert.ToDecimal(list[i].LFIMG));//数量
                    }*/
                    if (!list[i].LFIMG.IsNullOrEmpty() && !list[i].LFIMG.Equals("00000000"))
                    {//'芜湖佳景科技有限公司','内蒙古赢丰汽车有限公司','宜宾宜瑞汽车有限公司','集瑞联合卡车营销服务有限公司','集瑞联合重工有限公司') 这几个属于互转 不计

                        if ("芜湖佳景科技有限公司".Equals(list[i].WE_NAME) || "内蒙古赢丰汽车有限公司".Equals(list[i].WE_NAME) || "宜宾宜瑞汽车有限公司".Equals(list[i].WE_NAME) || "集瑞联合卡车营销服务有限公司".Equals(list[i].WE_NAME)|| "集瑞联合重工有限公司".Equals(list[i].WE_NAME))
                        {
                            delivery.LFIMG = 0;
                        }
                        else
                        {
                            delivery.LFIMG = Convert.ToInt32(Convert.ToDecimal(list[i].LFIMG));//数量
                        }


                    }
                    delivery.WERKS = list[i].WERKS; //工厂
                    if (!list[i].NETPR1.IsNullOrEmpty())
                    {
                        delivery.NETPR1 = Convert.ToDecimal(list[i].NETPR1); //上装成本
                    }
                    if (!list[i].NETPR.IsNullOrEmpty())
                    {
                        delivery.NETPR = Convert.ToDecimal(list[i].NETPR); //底盘采购成本
                    }
                    if (!list[i].STPRS.IsNullOrEmpty())
                    {
                        delivery.STPRS = Convert.ToDecimal(list[i].STPRS); //实际成本
                    }
                    if (!list[i].DMBTR2.IsNullOrEmpty())
                    {
                        delivery.DMBTR2 = Convert.ToDecimal(list[i].DMBTR2); //税金
                    }
                    if (!list[i].DMBTR.IsNullOrEmpty())
                    {
                        delivery.DMBTR = Convert.ToDecimal(list[i].DMBTR); //工厂
                    }
                    DBServerProvider.DbContext.Update(delivery);
                    
                }
                else //无值：添加
                {
                    big_data_delivery_note order = new big_data_delivery_note();
                    Guid id = Guid.NewGuid();
                    order.big_data_delivery_note_id = id;
                    order.sap_order_no = list[i].VBELN_S;//SAP销售订单号
                    order.sap_sales_voucher = list[i].VBELN_B; //SAP销售开票凭证
                    order.sap_sales_delivery_order_no = list[i].VBELN_J;//SAP销售交货单号
                    order.sap_dealer_customer_no = list[i].KUNNR;//SAP经销商客户的编号
                    order.dealer_code = list[i].AG_KUNNR;//运达方经销商ERP号
                    order.dealer_name = list[i].AG_NAME;//运达方经销商名称
                    order.sold_dealer_code = list[i].WE_KUNNR;//售达方经销商ERP号
                    order.sold_dealer_name = list[i].WE_NAME;//售达方经销商名称
                    if (!list[i].LTRMI.IsNullOrEmpty() && !list[i].LTRMI.Equals("00000000"))
                    {
                        order.in_stock_date = DateTime.ParseExact(list[i].LTRMI, "yyyyMMdd", CultureInfo.CurrentCulture);//入库时间
                    }
                    order.vin = list[i].SERNR;//SAP序列号（vin）
                    order.dms_order_no = list[i].BSTKD;//DMS销售订单号
                    order.MATNR = list[i].MATNR;//物料编码
                    order.Creator = "SAPADD";
                    order.CreateDate = DateTime.Now;
                    order.status = 0; //0未推送 1已推送
                    order.AUFNR = list[i].AUFNR;


                    if (!list[i].BUDAT.IsNullOrEmpty() && !list[i].BUDAT.Equals("00000000"))
                    {
                        order.BUDAT = DateTime.ParseExact(list[i].BUDAT, "yyyyMMdd", CultureInfo.CurrentCulture);//过账日期
                    }
                    if (!list[i].LFIMG.IsNullOrEmpty() && !list[i].LFIMG.Equals("00000000"))
                    {//'芜湖佳景科技有限公司','内蒙古赢丰汽车有限公司','宜宾宜瑞汽车有限公司','集瑞联合卡车营销服务有限公司')

                        if ("芜湖佳景科技有限公司".Equals(list[i].WE_NAME)|| "内蒙古赢丰汽车有限公司".Equals(list[i].WE_NAME)|| "宜宾宜瑞汽车有限公司".Equals(list[i].WE_NAME)|| "集瑞联合卡车营销服务有限公司".Equals(list[i].WE_NAME))
                        {
                            order.LFIMG = 0;
                        }
                        else
                        {
                            order.LFIMG = Convert.ToInt32(Convert.ToDecimal(list[i].LFIMG));//数量
                        }
                        

                    }
                    order.WERKS = list[i].WERKS; //工厂
                    if (!list[i].NETPR1.IsNullOrEmpty())
                    {
                        order.NETPR1 = Convert.ToDecimal(list[i].NETPR1); //上装成本
                    }
                    if (!list[i].NETPR.IsNullOrEmpty())
                    {
                        order.NETPR = Convert.ToDecimal(list[i].NETPR); //底盘采购成本
                    }
                    if (!list[i].STPRS.IsNullOrEmpty())
                    {
                        order.STPRS = Convert.ToDecimal(list[i].STPRS); //实际成本
                    }
                    if (!list[i].DMBTR2.IsNullOrEmpty())
                    {
                        order.DMBTR2 = Convert.ToDecimal(list[i].DMBTR2); //税金
                    }
                    if (!list[i].DMBTR.IsNullOrEmpty())
                    {
                        order.DMBTR = Convert.ToDecimal(list[i].DMBTR); //工厂
                    }
                    DBServerProvider.DbContext.Add(order);
                }
            }
           /* if (big_data_execute_parameter.manual_setting == 1 || !big_data_execute_parameter.parameter.Equal(newDate))
            {
                big_data_execute_parameter.parameter = newDate;
                big_data_execute_parameter.parameter1 = newDate;
                big_data_execute_parameter.manual_setting = 0;
                big_data_execute_parameter.Modifier = "系统";
                big_data_execute_parameter.ModifyDate = DateTime.Now;
                DBServerProvider.DbContext.Update(big_data_execute_parameter);
            }*/
            DBServerProvider.DbContext.SaveChanges();
            return list.Count;
        }

        /// <summary>
        /// 接口添加 开始日期和结束日期 查询针对核价完成时间
        /// DMS销售订单 添加核价完成时间字段 a.UPDATE_DATE AS 核价时间 from tt_sales_order a
        /// 从DMS系统里获取销售订单信息
        /// 
        /// 20230828更改逻辑：上一版是从SAP获取数据，发现存在业务上的问题（【一个销售订单可以多次交货，有多张交货单】），
        /// 现更改为从先DMS取数据 再从SAP获取数据并根据order_no去同步，以销售订单号为关联。
        /// </summary>
        /// <returns></returns>
        /// [ApiTask]
        [HttpPost, HttpGet, Route("getDmsOrderInfo"), AllowAnonymous]
        public object getDmsOrderInfo()
        {
            // 从参数表中获取对应的参数值
            big_data_execute_parameter big_data_execute_parameter = DBServerProvider.DbContext.Set<big_data_execute_parameter>().Where(x => x.type == "getDmsOrderInfo").FirstOrDefault();
            string startDate = big_data_execute_parameter.parameter;
            string endDate = big_data_execute_parameter.parameter1;
            string newDate = DateTime.Now.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
            if (big_data_execute_parameter.manual_setting == 0 && !startDate.Equal(newDate))
            {
                startDate = newDate;
                endDate = newDate;
            }
            string sql = "select a.order_no as dms_order_no, a.order_quantity ,a.create_date as order_create_date ,tfc3.code_name as order_status, tfc.Code_Name as order_car_classify, tfc1.Code_Name as order_nature, AA.ORG_NAME as order_org_name, decode(a.order_type, 10771001,'大客户', '经销商') as order_customer_classify,b.dealer_name as order_customer_name, (select tr.region_name from tm_region tr where tr.region_id = b.province_id) AS order_province,(select tr.region_name from tm_region tr where tr.region_id = b.city_id) as order_city, tfc2.Code_Name as order_sales, a.derive_material_code as order_material_code,tvm.material_name as order_material_name, a.UPDATE_DATE AS order_pricing_completion_date from tt_sales_order a,(SELECT TD.DEALER_CODE,TD.DEALER_NAME,TD.DEALER_ID,TD.PROVINCE_ID,TD.CITY_ID FROM TM_DEALER TD UNION SELECT TL.CUSTOMER_NO  AS DEALER_CODE,TL.CUSTOMER_NAME AS DEALER_NAME,TL.CUSTOMER_ID   AS DEALER_ID,NULL AS PROVINCE_ID, NULL AS CITY_ID FROM TT_LARGE_CUSTOMER TL) B, tm_vhcl_material tvm,(SELECT TOR.ORG_NAME, TDOE.DEALER_ID FROM TM_ORG TOR, TM_DEALER_ORG_RELATION TDOE WHERE TOR.ORG_ID = TDOE.ORG_ID AND TOR.duty_type = 11181004 and TOR.status = 10011001) AA,tm_fix_code tfc, tm_fix_code tfc1, tm_fix_code tfc2, tm_fix_code tfc3 where a.dealer_id = b.dealer_id AND A.VEHICLE_TYPE = tfc.Code(+) AND A.ORDER_FEATURES = tfc1.Code(+) and a.order_type = tfc2.Code(+) and a.order_status = tfc3.Code(+) AND A.DEALER_ID = AA.DEALER_ID(+) and tvm.material_code = a.derive_material_code and a.order_type in (10771001, 10771003) AND a.order_no IS NOT NULL AND a.order_status IN (10491005,10491009) AND TO_CHAR(a.UPDATE_DATE+2,'yyyy-MM-dd') >= '" + startDate+"' AND TO_CHAR(a.UPDATE_DATE,'yyyy-MM-dd') <= '"+endDate+"'  order by a.create_date desc ";
            //sql = sql.Replace("@_startDate@", startDate).Replace("@_endDate@",endDate);
            sql = sql.Replace("'", "''");//单引号转义
            List<big_data_sales_order> list = DBServerProvider.SqlDapper.QueryList<big_data_sales_order>("select * from openquery(DMS,'" + sql + "')", "").ToList();
            if (!list.IsNullOrEmpty())
            {
                for (int i = 0; i < list.Count; i++)
                {
                    string dms_order_no = list[i].dms_order_no;
                    big_data_sales_order order = DBServerProvider.DbContext.Set<big_data_sales_order>().Where(w => w.dms_order_no.Equals(dms_order_no)).ToList().LastOrDefault();
                    if (!order.IsNullOrEmpty()) //更新 
                    {
                        order.dms_order_no = list[i].dms_order_no;
                        order.order_status = list[i].order_status;
                        order.order_car_classify = list[i].order_car_classify;
                        order.order_nature = list[i].order_nature;
                        order.order_org_name = list[i].order_org_name;
                        order.order_customer_classify = list[i].order_customer_classify;
                        order.order_customer_name = list[i].order_customer_name;
                        order.order_province = list[i].order_province;
                        order.order_city = list[i].order_city;
                        order.order_sales = list[i].order_sales;
                        order.order_material_code = list[i].order_material_code;
                        order.order_material_name = list[i].order_material_name;
                        order.order_create_date = list[i].order_create_date;
                        order.order_pricing_completion_date = list[i].order_pricing_completion_date;
                        order.order_quantity = list[i].order_quantity;
                        order.Modifier = "DMSUPDATE";
                        order.ModifyDate = DateTime.Now;
                        DBServerProvider.DbContext.Update(order);
                    }
                    else //添加
                    {
                        big_data_sales_order salesorder = new big_data_sales_order();
                        salesorder.dms_order_no = list[i].dms_order_no;
                        salesorder.order_status = list[i].order_status;
                        salesorder.order_car_classify = list[i].order_car_classify;
                        salesorder.order_nature = list[i].order_nature;
                        salesorder.order_org_name = list[i].order_org_name;
                        salesorder.order_customer_classify = list[i].order_customer_classify;
                        salesorder.order_customer_name = list[i].order_customer_name;
                        salesorder.order_province = list[i].order_province;
                        salesorder.order_city = list[i].order_city;
                        salesorder.order_sales = list[i].order_sales;
                        salesorder.order_material_code = list[i].order_material_code;
                        salesorder.order_material_name = list[i].order_material_name;
                        salesorder.order_create_date = list[i].order_create_date;
                        salesorder.order_pricing_completion_date = list[i].order_pricing_completion_date;
                        salesorder.order_quantity = list[i].order_quantity;
                        salesorder.Creator = "DMSADD";
                        salesorder.CreateDate = DateTime.Now;
                        DBServerProvider.DbContext.Add(salesorder);
                    }
                   
                }
            }
            if (big_data_execute_parameter.manual_setting == 1 || !big_data_execute_parameter.parameter.Equal(newDate))
            {
                big_data_execute_parameter.parameter = newDate;
                big_data_execute_parameter.parameter1 = newDate;
                big_data_execute_parameter.manual_setting = 0;
                big_data_execute_parameter.Modifier = "系统";
                big_data_execute_parameter.ModifyDate = DateTime.Now;
                DBServerProvider.DbContext.Update(big_data_execute_parameter);
            }
            DBServerProvider.DbContext.SaveChanges();
            return list.Count;
        }

        class SomeFunctionParameters
        {
            [SapName("START_DATE")]
            public string START_DATE { get; set; }
            [SapName("END_DATE")]
            public string END_DATE { get; set; }
        }

        // <summary>
        /// 输出参数名称
        /// </summary>
        class SomeFunctionResult
        {
            [SapName("ZRETURN_SO")]
            public SomeFunctionitem[] ZRETURN_SO { get; set; }

        }

        /// <summary>
        /// 对应的SAP中返回的结构
        /// </summary>
        public class SomeFunctionitem
        {
            /**
             * SAP销售订单号
             */
            public string VBELN_S { get; set; } //SAP销售订单号
            public string VBELN_B { get; set; }//SAP销售开票凭证
            public string VBELN_J { get; set; }//SAP销售交货单号
            public string KUNNR { get; set; }//SAP经销商客户的编号
            public string AG_KUNNR { get; set; }//运达方经销商ERP号
            public string AG_NAME { get; set; }//运达方经销商名称
            public string WE_KUNNR { get; set; }//售达方经销商ERP号
            public string WE_NAME { get; set; }//售达方经销商名称
            public string LTRMI { get; set; }//入库时间
            public string SERNR { get; set; }//SAP序列号（vin）
            public string BSTKD { get; set; }//DMS销售订单号
            public string MATNR { get; set; }//物料编码
            public string AUFNR { get; set; }//生产订单号
            public string BUDAT { get; set; }//过账日期
            public string LFIMG { get; set; }//数量
            public string WERKS { get; set; }//工厂
            public string NETPR1 { get; set; }//上装成本
            public string NETPR { get; set; }//底盘采购成本
            public string STPRS { get; set; }//实际成本
            public string DMBTR2 { get; set; }//税金
            public string DMBTR { get; set; }//收入


        }
    }
}
